Skip Headers

Oracle® Call Interface Programmer's Guide, 10g Release 2 (10.2) Part Number B14250-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 5 Binding and Defining in OCI

This chapter contains these topics:

- Overview of Binding in OCI

- Advanced Bind Operations in OCI

- Overview of Defining in OCI

- Advanced Define Operations in OCI

- Binding and Defining Arrays of Structures in OCI

- DML with RETURNING Clause in OCI

- Character Conversion in OCI Binding and Defining

- PL/SQL REF CURSORs and Nested Tables in OCI

- Runtime Data Allocation and Piecewise Operations in OCI

## Overview of Binding in OCI

This chapter expands on the basic concepts of binding and defining, and provides more detailed information about the different types of binds and defines you can use in OCI applications. Additionally, this chapter discusses the use of arrays of structures, as well as other issues involved in binding, defining, and character conversions.

For example, given the `INSERT` statement

    INSERT INTO emp VALUES
        (:empno, :ename, :job, :sal, :deptno)

and the following variable declarations

    text     *ename, *job;
    sword    empno, sal, deptno;

the bind step makes an association between the placeholder name and the address of the program variables. The bind also indicates the datatype and length of the program variables, as illustrated in Figure 5-1.

See Also: The code that implements this example is found in the section "Steps Used in OCI Binding".

Figure 5-1 Using OCIBindByName() to Associate Placeholders with Program Variables

Description of "Figure 5-1 Using OCIBindByName() to Associate Placeholders with Program Variables"

If you change only the value of a bind variable, it is not necessary to rebind it in order to execute the statement again. Because the bind is by reference, as long as the address of the variable and handle remain valid, you can re-execute a statement that references the variable without rebinding.

Note: At the interface level, all bind variables are considered at least `IN` and must be properly initialized. If the variable is a pure `OUT` bind variable, you can set the variable to 0. You can also provide a `NULL` indicator and set that indicator to -1 (`NULL`).

In the Oracle server, new datatypes have been implemented for named datatypes, REFs and LOBs, and they may be bound as placeholders in a SQL statement.

Note: For opaque datatypes (descriptors or locators) whose sizes are not known, pass the address of the descriptor or locator pointer. Set the size parameter to the size of the appropriate data structure, (`sizeof(structure)`)

### Named Binds and Positional Binds

The SQL statement in the previous section is an example of a named bind. Each placeholder in the statement has a name associated with it, such as 'ename' or 'sal'. When this statement is prepared and the placeholders are associated with values in the application, the association is made by the name of the placeholder using the `OCIBindByName()` call with the name of the placeholder passed in the placeholder parameter.

A second type of bind is known as a positional bind. In a positional bind, the placeholders are referred to by their position in the statement rather than their names. For binding purposes, an association is made between an input value and the position of the placeholder, using the `OCIBindByPos()` call.

Using the previous example for a positional bind:

    INSERT INTO emp VALUES
        (:empno, :ename, :job, :sal, :deptno)

The five placeholders are then each bound by calling `OCIBindByPos()` and passing the position number of the placeholder in the `position` parameter. For example, the `:empno` placeholder would be bound by calling `OCIBindByPos()` with a position of 1, `:ename` with a position of 2, and so on.

In the case of a duplicate bind, only a single bind call may be necessary. Consider the following SQL statement, which queries the database for employees whose commission and salary are both greater than a given amount:

    SELECT empno FROM emp
        WHERE sal > :some_value
        AND comm > :some_value

An OCI application could complete the binds for this statement with a single call to `OCIBindByName()` to bind the `:some_value` placeholder by name. In this case, the second placeholder inherits the bind information from the first placeholder.

### OCI Array Interface

You can pass data to Oracle in various ways.

You can execute a SQL statement repeatedly using the `OCIStmtExecute()` routine and supply different input values on each iteration.

You can use the Oracle array interface and input many values with a single statement and a single call to `OCIStmtExecute()`. In this case you bind an array to an input placeholder, and the entire array can be passed at the same time, under the control of the iters parameter.

The array interface significantly reduces round trips to the database when you are updating or inserting a large volume of data. This reduction can lead to considerable performance gains in a busy client/server environment. For example, consider an application that needs to insert 10 rows into the database. Calling `OCIStmtExecute()` ten times with single values results in ten network round trips to insert all the data. The same result is possible with a single call to `OCIStmtExecute()` using an input array, which involves only one network round trip.

Note: When using the OCI array interface to perform inserts, row triggers in the database are fired as each row is inserted. The maximum number of rows allowed in an array DML statement is 4 gigabytes -1.

### Binding Placeholders in PL/SQL

You process a PL/SQL block by placing the block in a string variable, binding any variables, and then executing the statement containing the block, just as you would with a single SQL statement.

When you bind placeholders in a PL/SQL block to program variables, you must use `OCIBindByName()` or `OCIBindByPos()` to perform the basic binds for host variables that are either scalars or arrays.

The following short PL/SQL block contains two placeholders, which represent IN parameters to a procedure that updates an employee's salary, given the employee number and the new salary amount:

    char plsql_statement[] = "BEGIN\
                              RAISE_SALARY(:emp_number, :new_sal);\
                              END;" ;

These placeholders can be bound to input variables in the same way as placeholders in a SQL statement.

When processing PL/SQL statements, output variables are also associated with program variables using bind calls.

For example, in a PL/SQL block such as

    BEGIN
        SELECT ename,sal,comm INTO :emp_name, :salary, :commission
        FROM emp
        WHERE empno = :emp_number;
    END;

you would use `OCIBindByName()` to bind variables in place of the `:emp_name`, `:salary`, and `:commission` output placeholders, and in place of the input placeholder `:emp_number`.

Note: All buffers, even pure `OUT` buffers, must be initialized by setting the buffer length to zero in the bind call, or by setting the corresponding indicator to -1.

See Also: "Information for Named Datatype and REF Binds" for more information about binding PL/SQL placeholders

### Steps Used in OCI Binding

Placeholders are bound in several steps. For a simple scalar or array bind, it is only necessary to specify an association between the placeholder and the data, by using `OCIBindByName()` or `OCIBindByPos()`.

Once the bind is complete, the OCI library knows where to find the input data or where to put PL/SQL output data when the SQL statement is executed. Program input data does not need to be in the program variable when it is bound to the placeholder, but the data must be there when the statement is executed.

The following code example shows handle allocation and binding for each placeholder in a SQL statement.

    ...
    /* The SQL statement, associated with stmthp (the statement handle)
    by calling OCIStmtPrepare() */
    text *insert = (text *) "INSERT INTO emp(empno, ename, job, sal, deptno)\
        VALUES (:empno, :ename, :job, :sal, :deptno)";
    ...

    /*  Bind the placeholders in the SQL statement, one per bind handle. */
    checkerr(errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME",
        strlen(":ENAME"), (ub1 *) ename, enamelen+1, SOLT_STR, (dvoid *) 0,
        (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
    checkerr(errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":JOB",
        strlen(":JOB"), (ub1 *) job, joblen+1, SQLT_STR, (dvoid *)
        &job_ind, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
    checkerr(errhp, OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":SAL",
        strlen(":SAL"), (ub1 *) &sal, (sword) sizeof(sal), SQLT_INT,
        (dvoid *) &sal_ind, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,
        OCI_DEFAULT));
    checkerr(errhp, OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":DEPTNO",
        strlen(":DEPTNO"), (ub1 *) &deptno,(sword) sizeof(deptno), SQLT_INT,
        (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
    checkerr(errhp, OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO",
        strlen(":EMPNO"), (ub1 *) &empno, (sword) sizeof(empno), SQLT_INT,
        (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT));

Note: The `checkerr()` function evaluates the return code from an OCI application. The code for the function is listed in the section "OCI Programming Steps".

### PL/SQL Block in an OCI Program

Perhaps the most common use for PL/SQL blocks in OCI is to call stored procedures or stored functions. Assume that there is a procedure named RAISE_SALARY stored in the database, and you embed a call to that procedure in an anonymous PL/SQL block, and then process the PL/SQL block.

The following program fragment shows how to embed a stored procedure call in an OCI application. The program passes an employee number and a salary increase as inputs to a stored procedure called `raise_salary`:

    raise_salary (employee_num IN, sal_increase IN, new_salary OUT);

This procedure raises a given employee's salary by a given amount. The increased salary which results is returned in the stored procedure's variable, `new_salary`, and the program displays this value.

Note that the PL/SQL procedure argument, `new_salary`, although a PL/SQL OUT variable, must be bound, not defined. This is further explained in the section on OCI defines.

    /* Define PL/SQL statement to be used in program. */
    text *give_raise = (text *) "BEGIN\
                      RAISE_SALARY(:emp_number,:sal_increase, :new_salary);\
                         END;";
    OCIBind  *bnd1p = NULL;                      /* the first bind handle */
    OCIBind  *bnd2p = NULL;                     /* the second bind handle */
    OCIBind  *bnd3p = NULL;                      /* the third bind handle */

    static void checkerr();
    sb4 status;

    main()
    {
      sword    empno, raise, new_sal;
      dvoid    *tmp;
      OCISession *usrhp = (OCISession *)NULL;
    ...
    /* attach to database server, and perform necessary initializations
    and authorizations */
    ...
          /* allocate a statement handle */
      checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
               OCI_HTYPE_STMT, 100, (dvoid **) &tmp));

          /* prepare the statement request, passing the PL/SQL text
            block as the statement to be prepared */
    checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) give_raise, (ub4)
          strlen(give_raise), OCI_NTV_SYNTAX, OCI_DEFAULT));

          /* bind each of the placeholders to a program variable */
     checkerr( errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":emp_number",
                 -1, (ub1 *) &empno,
                (sword) sizeof(empno), SQLT_INT, (dvoid *) 0,
                 (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

     checkerr( errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":sal_increase",
                 -1, (ub1 *) &raise,
                 (sword) sizeof(raise), SQLT_INT, (dvoid *) 0,
                 (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

          /* remember that PL/SQL OUT variable are bound, not defined */

    checkerr( errhp, OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":new_salary",
                 -1, (ub1 *) &new_sal,
                 (sword) sizeof(new_sal), SQLT_INT, (dvoid *) 0,
                 (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

          /* prompt the user for input values */
    printf("Enter the employee number: ");
    scanf("%d", &empno);
          /* flush the input buffer */
    myfflush();

    printf("Enter employee's raise: ");
    scanf("%d", &raise);
          /* flush the input buffer */
    myfflush();

      /* execute PL/SQL block*/
      checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
          (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));

      /* display the new salary, following the raise */
    printf("The new salary is %d\n", new_sal);
    }

This example demonstrates how to perform a simple scalar bind where only a single bind call is necessary. In some cases, additional bind calls are needed to define attributes for specific bind datatypes or execution modes.

## Advanced Bind Operations in OCI

The section "Binding Placeholders in OCI" discussed how a basic bind operation is performed to create an association between a placeholder in a SQL statement and a program variable using `OCIBindByName()` or `OCIBindByPos()`. This section covers more advanced bind operations, including multi-step binds, and binds of named datatypes and `REFs`.

In some cases, additional bind calls are necessary to define specific attributes for certain bind datatypes or certain execution modes.

The following sections describe these special cases, and the information about binding is summarized in Table 5-1.

Table 5-1 Information Summary for Bind Types

Type of Bind | Bind Datatype | Notes
--- | --- | ---
Scalar | any scalar datatype | Bind a single scalar using `OCIBindByName()` or `OCIBindByPos()`.
Array of Scalars | any scalar datatype | Bind an array of scalars using `OCIBindByName()` or `OCIBindByPos()`.
Named Datatype | SQLT_NTY | Two bind calls are required: `OCIBindByName()` or `OCIBindByPos()` `OCIBindObject()`
REF | SQLT_REF | Two bind calls are required: `OCIBindByName()` or `OCIBindByPos()` `OCIBindObject()`
LOB `BFILE` | SQLT_BLOB SQLT_CLOB | Allocate the LOB locator using `OCIDescriptorAlloc()`, and then bind its address, `OCILobLocator` `**`, with `OCIBindByName()` or `OCIBindByPos()`, using one of the LOB datatypes.
Array of Structures or Static Arrays | varies | Two bind calls are required: `OCIBindByName()` or `OCIBindByPos()` `OCIBindArrayOfStruct()`
Piecewise Insert | varies | `OCIBindByName()` or `OCIBindByPos()` is required. The application may also need to call `OCIBindDynamic()` to register piecewise callbacks.
REF CURSOR variables | SQLT_RSET | Allocate a statement handle, `OCIStmt`, and then bind its address, `OCIStmt **`, using the SQLT_RSET datatype.

See Also: "Named Datatype Binds" For information on binding named datatypes (objects) "Binding REFs" for information on binding REFs

### Binding LOBs

There are two ways of binding LOBs:

- Bind the LOB locator, rather than the actual LOB values. In this case the LOB value is written or read by passing a LOB locator to the OCI LOB functions.

- Bind the LOB value directly, without using the LOB locator.

#### Binding LOB Locators

Either a single locator or an array of locators can be bound in a single bind call. In each case, the application must pass the address of a LOB locator and not the locator itself. For example, if an application has prepared a SQL statement:

    INSERT INTO some_table VALUES (:one_lob)

where `one_lob` is a bind variable corresponding to a LOB column, and has made the following declaration:

    OCILobLocator * one_lob;

Then the following calls would be used to bind the placeholder and execute the statement:

    /* initialize single locator */
    one_lob = OCIDescriptorAlloc(...OCI_DTYPE_LOB...);
    ...
    /* pass the address of the locator */
    OCIBindByName(...,(dvoid *) &one_lob,... SQLT_CLOB, ...);
    OCIStmtExecute(...,1,...)                /* 1 is the iters parameter */

You can also insert an array using the same SQL `INSERT` statement. In this case, the application would include the following code:

    OCILobLocator * lob_array[10];
    ...
    for (i=0; i<10, i++)
        lob_array[i] = OCIDescriptorAlloc(...OCI_DTYPE_LOB...);
                                         /* initialize array of locators */
    ...
    OCIBindByName(...,(dvoid *) lob_array,...);
    OCIBindArrayOfStruct(...);
    OCIStmtExecute(...,10,...);               /* 10 is the iters parameter */

You must allocate descriptors with the `OCIDescriptorAlloc()` routine before they can be used. In the case of an array of locators, you must initialize each array element using `OCIDescriptorAlloc()`. Use `OCI_DTYPE_LOB` as the `type` parameter when allocating `BLOBs`, `CLOBs`, and `NCLOBs`. Use `OCI_DTYPE_FILE` when allocating `BFILE`s.

#### Restrictions on Binding LOB Locators

- Piecewise and callback `INSERT` or `UPDATE` operations are not supported.

- When using a FILE locator as a bind variable for an `INSERT` or `UPDATE` statement, you must first initialize the locator with a directory object and filename, using `OCILobFileSetName()` before issuing the `INSERT` or `UPDATE` statement.

See Also: Chapter 7, "LOB and BFILE Operations" for more information about the OCI LOB functions

#### Binding LOB Data

Oracle allows nonzero binds for `INSERT`s and `UPDATE`s of any size LOB. So you can bind data into a LOB column using `OCIBindByPos()`, `OCIBindByName()`, and PL/SQL binds.

The bind of more than 4 kilobytes of data to a LOB column uses space from the temporary tablespace. Make sure that your temporary tablespace is big enough to hold at least the amount of data equal to the sum of all the bind lengths for LOBs. If your temporary tablespace is extendable, it will be extended automatically after the existing space is fully consumed. Use the following command to create an extendable temporary tablespace:

    CREATE TABLESPACE ... AUTOEXTENT ON ... TEMPORARY ...;

#### Restrictions on Binding LOB Data

- If a table has both `LONG` and LOB columns, then you can have binds of greater than 4 kilobytes for either the `LONG` column or the LOB columns, but not both in the same statement.

- In an `INSERT` `AS` `SELECT` operation, Oracle does not allow binding of any length data to LOB columns.

- Oracle does not do implicit conversions, such as `HEX` to `RAW` or `RAW` to `HEX,` for data of size more than 4000 bytes. The following PL/SQL code illustrates this:

      create table t (c1 clob, c2 blob);
      declare
        text   varchar(32767);
        binbuf raw(32767);
      begin
        text := lpad ('a', 12000, 'a');
        binbuf := utl_raw.cast_to_raw(text);

        -- The following works:
        insert into t values (text, binbuf);

        -- The following won't work because Oracle won't do implicit
        -- hex to raw conversion.
        insert into t (c2) values (text);

        -- The following won't work because Oracle won't do implicit
        -- raw to hex conversion.
        insert into t (c1) values (binbuf);

        -- The following won't work because we can't combine the
        -- utl_raw.cast_to_raw() operator with the >4k bind.
        insert into t (c2) values (utl_raw.cast_to_raw(text));

      end;
      /

- If you bind more than 4000 bytes of data to a `BLOB` or a `CLOB`, and the data is filtered by a SQL operator, then Oracle will limit the size of the result to at most 4000 bytes.

  For example:

      create table t (c1 clob, c2 blob);
      -- The following command inserts only 4000 bytes because the result of
      -- LPAD is limited to 4000 bytes
      insert into t(c1) values (lpad('a', 5000, 'a'));

      -- The following command inserts only 2000 bytes because the result of
      -- LPAD is limited to 4000 bytes, and the implicit hex to raw conversion
      -- converts it to 2000 bytes of RAW data.
      insert into t(c2) values (lpad('a', 5000, 'a'));

#### Examples of Binding LOB Data

Consider the following SQL statements which will be used in the examples that follow:

    CREATE TABLE foo (a INTEGER );
    CREATE TYPE lob_typ AS OBJECT (A1 CLOB );
    CREATE TABLE lob_long_tab (C1 CLOB, C2 CLOB, CT3 lob_typ, L LONG);

##### Example1: Binding LOBs

    void insert()                 /* A function in an OCI program */
    {
       /* The following is allowed */
       ub1 buffer[8000];
       text *insert_sql = (text *) "INSERT INTO lob_long_tab (C1, C2, L) \
                           VALUES (:1, :2, :3)";
       OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                                  (OCISnapshot *) NULL, OCI_DEFAULT);
    }

##### Example2: Binding LOBs

    void insert()
    {
       /* The following is allowed */
       ub1 buffer[8000];
       text *insert_sql = (text *) "INSERT INTO lob_long_tab (C1, L) \
                          VALUES (:1, :2)";
       OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                                  (OCISnapshot *) NULL, OCI_DEFAULT);
    }

##### Example3: Binding LOBs

    void update()
    {
       /* The following is allowed, no matter how many rows it updates */
       ub1 buffer[8000];
       text *update_sql = (text *)"UPDATE lob_long_tab SET \
                                  C1 = :1, C2=:2, L=:3";
       OCIStmtPrepare(stmthp, errhp, update_sql, strlen((char*)update_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                                  (OCISnapshot *) NULL, OCI_DEFAULT);
    }

##### Example4: Binding LOBs

    void update()
    {
       /* The following is allowed, no matter how many rows it updates */
       ub1 buffer[8000];
       text *update_sql = (text *)"UPDATE lob_long_tab SET \
                                   C1 = :1, C2=:2, L=:3";
       OCIStmtPrepare(stmthp, errhp, update_sql, strlen((char*)update_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 2000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                                   (OCISnapshot *) NULL, OCI_DEFAULT);
    }

##### Example5: Binding LOBs

    void insert()
    {
       /* Piecewise, callback and array insert/update operations similar to
        * the allowed regular insert/update operations are also allowed */
    }

##### Example6: Binding LOBs

    void insert()
    {
       /* The following is NOT allowed because we try to insert >4000 bytes
        * into both LOB and LONG columns */
       ub1 buffer[8000];
       text *insert_sql = (text *)"INSERT INTO lob_long_tab (C1, L) \
                                   VALUES (:1, :2)";
       OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                                  (OCISnapshot *) NULL, OCI_DEFAULT);
    }

##### Example7: Binding LOBs

    void insert()
    {
       /* Insert of data into LOB attributes is allowed */
       ub1 buffer[8000];
       text *insert_sql = (text *)"INSERT INTO lob_long_tab (CT3) \
                                   VALUES (lob_typ(:1))";
       OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                                  (OCISnapshot *) NULL, OCI_DEFAULT);
    }

##### Example8: Binding LOBs

    void insert()
    {
       /* The following is NOT allowed because we try to do insert as
        * select character data into LOB column */
       ub1 buffer[8000];
       text *insert_sql = (text *)"INSERT INTO lob_long_tab (C1) SELECT \
                                   :1 from FOO";
       OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,
                    SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                                  (OCISnapshot *) NULL, OCI_DEFAULT);
    }

### Binding in OCI_DATA_AT_EXEC Mode

If the `mode` parameter in a call to `OCIBindByName()` or `OCIBindByPos()` is set to `OCI_DATA_AT_EXEC`, an additional call to `OCIBindDynamic()` is necessary if the application will use the callback method for providing data at runtime. The call to `OCIBindDynamic()` sets up the callback routines, if necessary, for indicating the data or piece provided. If the `OCI_DATA_AT_EXEC` mode is chosen, but the standard OCI piecewise polling method will be used instead of callbacks, the call to `OCIBindDynamic()` is not necessary.

When binding `RETURN` clause variables, an application must use `OCI_DATA_AT_EXEC` mode, and it must provide callbacks.

See Also: "Runtime Data Allocation and Piecewise Operations in OCI" for more information about piecewise operations

### Binding REF CURSOR Variables

REF CURSORs are bound to a statement handle with a bind datatype of SQLT_RSET.

See Also: "PL/SQL REF CURSORs and Nested Tables in OCI"

## Overview of Defining in OCI

Query statements return data from the database to your application. When processing a query, you must define an output variable or an array of output variables for each item in the select-list for retrieving data. The define step creates an association that determines where returned results are stored, and in what format.

For example, if your program processes the following statement you would normally need to define two output variables, one to receive the value returned from the `name` column, and one to receive the value returned from the `ssn` column:

    SELECT name, ssn FROM employees
        WHERE empno = :empnum

If you were only interested in retrieving values from the `name` column, you would not need to define an output variable for `ssn`. If the `SELECT` statement being processed returns more than a single row for a query, the output variables you define can be arrays instead of scalar values.

Depending on the application, the define step can take place before or after an execute. If you know the datatypes of select-list items at compile time, the define can take place before the statement is executed. If your application is processing dynamic SQL statements entered by you at runtime or statements that do not have a clearly defined select-list, the application must execute the statement to retrieve describe information. After the describe information is retrieved, the type information for each select-list item is available for use in defining output variables.

The OCI processes the define call locally on the client side. In addition to indicating the location of buffers where results should be stored, the define step determines what data conversions must take place when data is returned to the application.

Note: Output buffers must be 2-byte aligned.

The `dty` parameter of the `OCIDefineByPos()` call specifies the datatype of the output variable. The OCI is capable of a wide range of data conversions when data is fetched into the output variable. For example, internal data in Oracle `DATE` format can be automatically converted to a `String` datatype on output.

See Also: Chapter 3, "Datatypes" For more information about datatypes and conversions "Describing Select-list Items" for more information

### Steps Used in OCI Defining

A basic define is done with a position call, `OCIDefineByPos()`. This step creates an association between a select-list item and an output variable. Additional define calls may be necessary for certain datatypes or fetch modes. Once the define step is complete, the OCI library determines where to put retrieved data. You can make your define calls again to redefine the output variables without having to re-prepare or re-execute the SQL statement.

The following example shows a scalar output variable being defined following an execute and a describe.

    SELECT department_name FROM departments WHERE department_id = :dept_input

       /* The input placeholder was bound earlier, and the data comes from the
       user input below */

      printf("Enter employee dept: ");
      scanf("%d", &deptno);

     /* Execute the statement. If OCIStmtExecute() returns OCI_NO_DATA, meaning that
        no data matches the query, then the department number is invalid. */

      if ((status = OCIStmtExecute(svchp, stmthp, errhp, 0, 0, (OCISnapshot *) 0, (OCISnapshot *) 0,
           OCI_DEFAULT))
          && (status != OCI_NO_DATA))
      {
        checkerr(errhp, status);
        return OCI_ERROR;
      }
      if (status == OCI_NO_DATA) {
        printf("The dept you entered doesn't exist.\n");
        return 0;
      }

       /* The next two statements describe the select-list item, dname, and
       return its length */
      checkerr(errhp, OCIParamGet((dvoid *)stmthp, (ub4) OCI_HTYPE_STMT, errhp, (dvoid **)&parmdp, (ub4) 1));
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
            (dvoid*) &deptlen, (ub4 *) &sizelen, (ub4) OCI_ATTR_DATA_SIZE,
            (OCIError *) errhp  ));

      /* Use the retrieved length of dname to allocate an output buffer, and
       then define the output variable. If the define call returns an error,
       exit the application */
      dept = (text *) malloc((int) deptlen + 1);
      if (status = OCIDefineByPos(stmthp, &defnp, errhp,
                 1, (dvoid *) dept, (sb4) deptlen+1,
                 SQLT_STR, (dvoid *) 0, (ub2 *) 0,
                 (ub2 *) 0, OCI_DEFAULT))
      {
        checkerr(errhp, status);
        return OCI_ERROR;
      }

See Also: "Describing Select-list Items" for an explanation of the describe step

### Advanced OCI Defines

In some cases the define step requires more than just a call to `OCIDefineByPos()`. There are additional calls that define the attributes of an array fetch, `OCIDefineArrayOfStruct(),` or a named datatype fetch, `OCIDefineObject()`. For example, to fetch multiple rows with a column of named datatypes, all three calls must be invoked for the column; but to fetch multiple rows of scalar columns, `OCIDefineArrayOfStruct()` and `OCIDefineByPos()` are sufficient.

Oracle also provides pre-defined C datatypes that map object type attributes.

See Also: Chapter 11, "Object-Relational Datatypes in OCI" "Advanced Define Operations in OCI"

## Advanced Define Operations in OCI

This section covers advanced defined operations, including multi-step defines, and defines of named datatypes and REFs.

In some cases the define step requires additional calls that define the attributes of an array fetch, `OCIDefineArrayOfStruct()`, or a named datatype fetch, `OCIDefineObject()`. For example, to fetch multiple rows with a column of named datatypes, all the three calls must be invoked for the column. To fetch multiple rows of scalar columns only `OCIDefineArrayOfStruct()` and `OCIDefineByPos()` are sufficient.

### Defining LOB Output Variables

There are two ways of defining LOBs:

- Define as a LOB locator, rather than the actual LOB values. In this case the LOB value is written or read by passing a LOB locator to the OCI LOB functions.

- Define as a LOB value directly, without using the LOB locator.

#### Defining LOB Locators

Either a single locator or an array of locators can be defined in a single define call. In each case, the application must pass the address of a LOB locator and not the locator itself. For example, if an application has prepared a SQL statement like:

    SELECT lob1 FROM some_table;

where `lob1` is the LOB column and `one_lob` is a define variable corresponding to a LOB column with the following declaration:

    OCILobLocator * one_lob;

The following sequence of steps bind the placeholder, and execute the statement:

    /* initialize single locator */
    one_lob = OCIDescriptorAlloc(...OCI_DTYPE_LOB...);
    ...
    /* pass the address of the locator */
    OCIDefineByPos(... 1, ...,(dvoid *) &one_lob,... SQLT_CLOB, ...);
    OCIStmtExecute(...,1,...)                /* 1 is the iters parameter */

You can also do an array select using the same SQL `SELECT` statement. In this case, the application would include the following steps:

    OCILobLocator * lob_array[10];
    ...
    for (i=0; i<10, i++)
       lob_array[i] = OCIDescriptorAlloc(...OCI_DTYPE_LOB...);
                                         /* initialize array of locators */
    ...
    OCIDefineByPos(...,1, (dvoid *) lob_array,... SQLT_CLOB, ...);
    OCIDefineArrayOfStruct(...);
    OCIStmtExecute(...,10,...);               /* 10 is the iters parameter */

Note that you must allocate descriptors with the `OCIDescriptorAlloc()` routine before they can be used. In the case of an array of locators, you must initialize each array element using `OCIDescriptorAlloc()`. Use `OCI_DTYPE_LOB` as the `type` parameter when allocating `BLOBs`, `CLOBs`, and `NCLOBs`. Use `OCI_DTYPE_FILE` when allocating `BFILE`s.

#### Defining LOB Data

Oracle allows nonzero defines for `SELECTs` of any size LOB. So you can select up to the maximum allowed size of data from a LOB column using `OCIDefineByPos()`, and PL/SQL defines. Because there can be multiple LOBs in a row, you can select the maximum size of data from each one of those LOBs in the same `SELECT` statement.

Consider the following SQL statements which will be used in the examples that follow:

    CREATE TABLE lob_tab (C1 CLOB, C2 CLOB);

#### Example1: Defining LOBs Before Execution

    void select_define_before_execute()      /* A function in an OCI program */
    {
      /* The following is allowed */
       ub1 buffer1[8000];
       ub1 buffer2[8000];
       text *select_sql = (text *)"SELECT c1, c2 FROM lob_tab";

       OCIStmtPrepare(stmthp, errhp, select_sql, (ub4)strlen((char*)select_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIDefineByPos(stmthp, &defhp[0], errhp, 1, (dvoid *)buffer1, 8000,
                    SQLT_LNG, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
       OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (dvoid *)buffer2, 8000,
                    SQLT_LNG, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *)0,
                     (OCISnapshot *)0, OCI_DEFAULT);
    }

#### Example2: Defining LOBs after Execution

    void select_execute_before_define()
    {
      /* The following is allowed */
       ub1 buffer1[8000];
       ub1 buffer2[8000];
       text *select_sql = (text *)"SELECT c1, c2 FROM lob_tab";

       OCIStmtPrepare(stmthp, errhp, select_sql, (ub4)strlen((char*)select_sql),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
       OCIStmtExecute(svchp, stmthp, errhp, 0, 0, (OCISnapshot *)0,
                     (OCISnapshot *)0, OCI_DEFAULT);
       OCIDefineByPos(stmthp, &defhp[0], errhp, 1, (dvoid *)buffer1, 8000,
                     SQLT_LNG, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
       OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (dvoid *)buffer2, 8000,
                     SQLT_LNG, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
       OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
    }

### Defining PL/SQL Output Variables

Do not use the define calls to define output variables for select-list items in a SQL SELECT statement inside a PL/SQL block. Use OCI bind calls instead.

See Also: "Information for Named Datatype and REF Defines, and PL/SQL OUT Binds" for more information about defining PL/SQL output variables.

### Defining for a Piecewise Fetch

A piecewise fetch requires an initial call to `OCIDefineByPos()`. An additional call to `OCIDefineDynamic()` is necessary if the application will use callbacks rather than the standard polling mechanism.

## Binding and Defining Arrays of Structures in OCI

Defining arrays of structures requires an initial call to `OCIDefineByPos().` An additional call to `OCIDefineArrayOfStruct()` is necessary to set up each additional parameter, including the `skip` parameter necessary for arrays of structures operations.

Using arrays of structures can simplify the processing of multi-row, multi-column operations. You can create a structure of related scalar data items, and then fetch values from the database into an array of these structures, or insert values into the database from an array of these structures.

For example, an application may need to fetch multiple rows of data from columns `NAME`, `AGE`, and `SALARY`. The application can include the definition of a structure containing separate fields to hold the `NAME`, `AGE` and `SALARY` data from one row in the database table. The application would then fetch data into an array of these structures.

In order to perform a multi-row, multi-column operation using an array of structures, associate each column involved in the operation with a field in a structure. This association, which is part of `OCIDefineArrayOfStruct()` and `OCIBindArrayOfStruct()` calls, specifies where data is stored.

### Skip Parameters

When you split column data across an array of structures, it is no longer stored contiguously in the database. The single array of structures stores data as though it were composed of several arrays of scalars. For this reason, you must specify a skip parameter for each field you are binding or defining. This skip parameter is the number of bytes that need to be skipped in the array of structures before the same field is encountered again. In general, this will be equivalent to the byte size of one structure.

Figure 5-2 shows how a skip parameter is determined. In this case the skip parameter is the sum of the sizes of the fields `field1,` `field2,` and `field3`, which is 8 bytes. This equals the size of one structure.

Figure 5-2 Determining Skip Parameters

Description of "Figure 5-2 Determining Skip Parameters"

On some operating systems it may be necessary to set the skip parameter to `sizeof`(`one_array_element`) rather than `sizeof`(`struct`), because some compilers insert extra bytes into a structure.

Consider an array of C structures consisting of two fields, a `ub4` and a `ub1`:

    struct demo {
        ub4 field1;
        ub1 field2;
    };
    struct demo demo_array[MAXSIZE];

Some compilers insert three bytes of padding after the `ub1` so that the `ub4` which begins the next structure in the array is properly aligned. In this case, the following statement may return an incorrect value:

    skip_parameter = sizeof(struct demo);

On some operating systems this will produce a proper skip parameter of eight. On other systems, `skip_parameter` will be set to five bytes by this statement. In this case, use the following statement to get the correct value for the skip parameter:

    skip_parameter = sizeof(demo_array[0]);

#### Skip Parameters for Standard Arrays

Arrays of structures are an extension of binding and defining arrays of single variables. When specifying a single-variable array operation, the related skip will be equal to the size of the datatype of the array under consideration. For example, for an array declared as:

    text emp_names[4][20];

the skip parameter for the bind or define operation will be 20. Each data element in the array is then recognized as a separate unit, rather than being part of a structure.

### OCI Calls Used with Arrays of Structures

Two OCI calls must be used when performing operations involving arrays of structures:

- `OCIBindArrayOfStruct()` for binding fields in arrays of structures for input variables

- `OCIDefineArrayOfStruct()` for defining arrays of structures for output variables.

  Note: When binding or defining for arrays of structures, multiple calls are required. A call to `OCIBindByName()` or `OCIBindByPos()` must precede a call to `OCIBindArrayOfStruct()`, and a call to `OCIDefineByPos()` must precede a call to `OCIDefineArrayOfStruct()`.

### Arrays of Structures and Indicator Variables

The implementation of arrays of structures also supports the use of indicator variables and return codes. You can declare parallel arrays of column-level indicator variables and return codes that correspond to the arrays of information being fetched, inserted, or updated. These arrays can have their own skip parameters, which are specified during `OCIBindArrayOfStruct()` or `OCIDefineArrayOfStruct()` calls.

You can set up arrays of structures of program values and indicator variables in many ways. Consider an application that fetches data from three database columns into an array of structures containing three fields. You can set up a corresponding array of indicator variable structures of three fields, each of which is a column-level indicator variable for one of the columns being fetched from the database. A one-to-one relationship between the fields in an indicator struct and the number of select-list items is not necessary.

See Also: "Indicator Variables" for more information about indicator variables.

## DML with RETURNING Clause in OCI

OCI supports the use of the `RETURNING` clause with SQL `INSERT`, `UPDATE`, and `DELETE` statements. This section outlines the rules for correctly implementing DML statements with the `RETURNING` clause.

See Also: For a complete examples, see the demonstration programs included with your Oracle installation. For additional information, refer to Appendix B, "OCI Demonstration Programs" Oracle Database SQL Reference. or more information about the use of the `RETURNING` clause with `INSERT`, `UPDATE`, or `DELETE` statements

### Using DML with RETURNING Clause

Using the `RETURNING` clause with a DML statement enables you to combine two SQL statements into one, possibly saving you a server round trip. This is accomplished by adding an extra clause to the traditional `UPDATE`, `INSERT`, and `DELETE` statements. The extra clause effectively adds a query to the DML statement.

In OCI, values are returned to the application as `OUT` bind variables. In the following examples, the bind variables are indicated by a preceding colon, ":". These examples assume the existence of `table1`, a table that contains columns `col1`, `col2`, and `col3`.

The following statement inserts new values into the database and then retrieves the column values of the affected row from the database, for manipulating inserted rows.

    INSERT INTO table1 VALUES (:1, :2, :3)
         RETURNING col1, col2, col3
         INTO :out1, :out2, :out3

The next example updates the values of all columns where the value of `col1` falls within a given range, and then returns the affected rows which were modified.

    UPDATE table1 SET col1 = col1 + :1, col2 = :2, col3 = :3
         WHERE col1 >= :low AND col1 <= :high
         RETURNING col1, col2, col3
         INTO :out1, :out2, :out3

The `DELETE` statement deletes the rows where `col1` value falls within a given range, and then returns the data from those rows.

    DELETE FROM table1 WHERE col1 >= :low AND col2 <= :high
         RETURNING col1, col2, col3
         INTO :out1, :out2, :out3

### Binding RETURNING...INTO variables

Because both the `UPDATE` and `DELETE` statements can affect multiple rows in the table, and a DML statement can be executed multiple times in a single `OCIExecute()` call, how much data will be returned may not be known at runtime. As a result, the variables corresponding to the `RETURNING`...`INTO` placeholders must be bound in `OCI_DATA_AT_EXEC` mode. An application must define its own dynamic data handling callbacks rather than using a polling mechanism.

The returning clause can be particularly useful when working with LOBs. Normally, an application must insert an empty LOB locator into the database, and then `SELECT` it back out again to operate on it. Using the `RETURNING` clause, the application can combine these two steps into a single statement:

    INSERT INTO some_table VALUES (:in_locator)
        RETURNING lob_column
        INTO :out_locator

An OCI application implements the placeholders in the `RETURNING` clause as pure `OUT` bind variables. However, all binds in the `RETURNING` clause are initially `IN` and must be properly initialized. To provide a valid value, you can provide a `NULL` indicator and set that indicator to -1.

An application must adhere to the following rules when working with bind variables in a `RETURNING` clause:

1. Bind `RETURNING` clause placeholders in `OCI_DATA_AT_EXEC` mode using `OCIBindByName()` or `OCIBindByPos()`, followed by a call to `OCIBindDynamic()` for each placeholder.

2. When binding `RETURNING` clause placeholders, supply a valid `OUT` bind function as the `ocbfp` parameter of the `OCIBindDynamic()` call. This function must provide storage to hold the returned data.

3. The `icbfp` parameter of `OCIBindDynamic()` call should provide a default function which returns `NULL` values when called.

4. The `piecep` parameter of `OCIBindDynamic()` must be set to `OCI_ONE_PIECE`.

No duplicate binds are allowed in a DML statement with a `RETURNING` clause, and no duplication between bind variables in the DML section and the `RETURNING` section of the statement is allowed.

Note: The OCI only supports the callback mechanism for `RETURNING` clause binds. The polling mechanism is not supported.

### OCI Error Handling

The `OUT` bind function provided to `OCIBindDynamic()` must be prepared to receive partial results of a statement in the event of an error. If the application has issued a DML statement that is executed 10 times, and an error occurs during the fifth iteration, the server returns the data from iterations 1 through 4. The callback function is still called to receive data for the first four iterations.

### DML with RETURNING REF...INTO Clause in OCI

The `RETURNING` clause can also be used to return a `REF` to an object which is being inserted into or updated in the database:

    UPDATE extaddr e SET e.zip = '12345', e.state ='AZ'
        WHERE e.state = 'CA' AND e.zip = '95117'
        RETURNING REF(e), zip
        INTO :addref, :zip

The preceding statement updates several attributes of an object in an object table and returns a REF to the object (and a scalar ZIP code) in the `RETURNING` clause.

#### Binding the Output Variable

Binding the `REF` output variable in an OCI application requires three steps:

1. The initial bind information is set using `OCIBindByName()`

2. Additional bind information for the `REF` (including the TDO) is set with `OCIBindObject()`

3. A call to `OCIBindDynamic()`

The following pseudocode shows a function which performs the binds necessary for the preceding example.

    sword bind_output(stmthp, bndhp, errhp)
    OCIStmt *stmthp;
    OCIBind *bndhp[];
    OCIError *errhp;
    {
      ub4 i;
                                      /* get TDO for BindObject call */
      if (OCITypeByName(envhp, errhp, svchp, (CONST text *) 0,
                       (ub4) 0, (CONST text *) "ADDRESS_OBJECT",
                       (ub4) strlen((CONST char *) "ADDRESS_OBJECT"),
                       (CONST text *) 0, (ub4) 0,
                        OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, &addrtdo))
      {
        return OCI_ERROR;
      }

                             /* initial bind call for both variables */
      if (OCIBindByName(stmthp, &bndhp[2], errhp,
                           (text *) ":addref", (sb4) strlen((char *) ":addref"),
                           (dvoid *) 0, (sb4) sizeof(OCIRef *), SQLT_REF,
                           (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                           (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
      ||  OCIBindByName(stmthp, &bndhp[3], errhp,
                           (text *) ":zip", (sb4) strlen((char *) ":zip"),
                           (dvoid *) 0, (sb4) MAXZIPLEN, SQLT_CHR,
                           (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                           (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC))
      {
        return OCI_ERROR;
      }

                                     /* object bind for REF variable */
      if (OCIBindObject(bndhp[2], errhp, (OCIType *) addrtdo,
              (dvoid **) &addrref[0], (ub4 *) 0, (dvoid **) 0, (ub4 *) 0))
      {
        return OCI_ERROR;
      }

      for (i = 0; i < MAXCOLS; i++)
        pos[i] = i;
                        /* dynamic binds for both RETURNING variables */
      if (OCIBindDynamic(bndhp[2], errhp, (dvoid *) &pos[0], cbf_no_data,
                        (dvoid *) &pos[0], cbf_get_data)
      ||  OCIBindDynamic(bndhp[3], errhp, (dvoid *) &pos[1], cbf_no_data,
                        (dvoid *) &pos[1], cbf_get_data))
      {
        return OCI_ERROR;
      }

      return OCI_SUCCESS;
    }

### Additional Notes About OCI Callbacks

When a callback function is called, the `OCI_ATTR_ROWS_RETURNED` attribute of the bind handle tells the application the number of rows being returned in that particular iteration. During the first callback of an iteration you can allocate space for all rows that are returned for that bind variable. During subsequent callbacks of the same iteration, you merely increment the buffer pointer to the correct memory within the allocated space.

### Array Interface for DML RETURNING Statements in OCI

OCI provides additional functionality for single-row DML and array DML operations in which each iteration returns more than one row. To take advantage of this feature, you must specify an OUT buffer in the bind call that is at least as big as the iteration count specified by the `OCIStmtExecute()` call. This is in addition to the bind buffers provided through callbacks.

If any of the iteration returns more than one row, then the application receives an `OCI_SUCCESS_WITH_INFO` return code. In this case, the DML operation is successful. At this point the application may choose to roll back the transaction or ignore the warning.

## Character Conversion in OCI Binding and Defining

This section discusses issues involving character conversions between the client and the server.

### Choosing Character Set

If a database column containing character data is defined to be an `NCHAR` or `NVARCHAR2` column, then a bind or define involving that column must take into account special considerations for dealing with character set specifications.

These considerations are necessary in case the width of the client character set is different from the server character set, and also for proper character conversion. During conversion of data between different character sets, the size of the data may increase or decrease by a factor of four. Insure that buffers provided to hold the data are of sufficient size.

In some cases, it may also be easier for an application to deal with `NCHAR` or `NVARCHAR2` data in terms of numbers of characters, rather than numbers of bytes, which is the usual case.

#### Character Set Form and ID

Each OCI bind and define handle has `OCI_ATTR_CHARSET_FORM` and `OCI_ATTR_CHARSET_ID` attributes associated. An application can set these attributes with the `OCIAttrSet()` call in order to specify the character form and character set ID of the bind/define buffer.

The `csform` attribute (`OCI_ATTR_CHARSET_FORM`) indicates the character set of the client buffer, for binds, and the character set in which to store fetched data for defines. It has two possible values:

- SQLCS_IMPLICIT - default value, indicates database character set ID for the bind or define buffer and the character buffer data are converted to the server database character set

- SQLCS_NCHAR - indicates that the national character set ID for the bind or define buffer and the client buffer data are converted to the server national character set.

If the character set ID attribute, `OCI_ATTR_CHARSET_ID`, is not specified, either the default value of the database or the national character set ID of the client is used, depending on the value of `csform`. They are the values specified in the NLS_LANG and NLS_NCHAR environment variables, respectively

Note: The data is converted and inserted into the database according to the server's database character set ID or national character set ID, regardless of the client-side character set id. `OCI_ATTR_CHARSET_ID` must never be set to 0. The define handle attributes `OCI_ATTR_CHARSET_FORM` and `OCI_ATTR_CHARSET_ID` do not affect the LOB types. LOB locators fetched from the server retain their original `csform`s. There is no CLOB/NCLOB conversion as part of define conversion based on these attributes.

See Also: Oracle Database Reference for more information about `NCHAR` data

#### Implicit Conversion Between CHAR and NCHAR

As the result of implicit conversion between database character sets and national character sets, OCI can support cross binding and cross defining between `CHAR` and `NCHAR`. Even though the `OCI_ATTR_CHARSET_FORM` attribute is set to `SQLCS_NCHAR`, OCI enables conversion of data to the database character set if the data is inserted into a `CHAR` column.

### Setting Client Character Sets in OCI

You can set the character sets through the `OCIEnvNlsCreate()` function parameters `charset` and `ncharset`. Both of these parameters can be set as `OCI_UTF16ID`. The `charset` parameter controls coding of the metadata and `CHAR` data. `ncharset` controls coding of `NCHAR` data. The function `OCINlsEnvironmentVariableGet()` returns the character set from NLS_LANG and the national character set from NLS_NCHAR.

Here is an example of the use of these functions (OCI provides a typedef called `utext` to facilitate binding and defining of UTF-16 data):

    OCIEnv *envhp;
    ub2 ncsid = 2; /* we8dec */
    ub2 hdlcsid, hdlncsid;
    OraText thename[20];
    utext *selstmt = L"SELECT ename FROM emp"; /* UTF16 statement */
    OCIStmt *stmthp;
    OCIDefine *defhp;
    OCIError *errhp;
    OCIEnvNlsCreate(OCIEnv **envhp, ..., OCI_UTF16ID, ncsid);
    ...
    OCIStmtPrepare(stmthp, ..., selstmt, ...); /* prepare UTF16 statement */
    OCIDefineByPos(stmthp, defnp, ..., 1, thename, sizeof(thename), SQLT_CHR,...);
    OCINlsEnvironmentVariableGet(&hdlcsid, (size_t)0, OCI_NLS_CHARSET_ID, (ub2)0,
         (size_t*)NULL);
    OCIAttrSet(defnp, ..., &hdlcsid, 0, OCI_ATTR_CHARSET_ID, errhp);
               /* change charset ID to NLS_LANG setting*/
    ...

See Also: "OCIEnvNlsCreate()" "OCINlsEnvironmentVariableGet()"

### Using OCI_ATTR_MAXDATA_SIZE Attribute

Update or insert operations are done through variable binding. When binding variables, specify `OCI_ATTR_MAXCHAR_SIZE` and `OCI_ATTR_MAXDATA_SIZE` in the bind handle to indicate character and byte constraints used when inserting data on the server.

These attributes are defined as:

- `OCI_ATTR_MAXCHAR_SIZE` sets the maximum number of characters allowed in the buffer on the server side.

- `OCI_ATTR_MAXDATA_SIZE` sets the maximum number of bytes allowed in the buffer on the server side.

Every bind handle has an `OCI_ATTR_MAXDATA_SIZE` attribute that specifies the number of bytes allocated on the server to accommodate client-side bind data after character set conversions.

An application will typically set `OCI_ATTR_MAXDATA_SIZE` to the maximum size of the column or the size of the PL/SQL variable, depending on how it is used. Oracle issues an error if `OCI_ATTR_MAXDATA_SIZE` is not large enough to accommodate the data after conversion, and the operation will fail.

For `IN/INOUT` binds, when OCI_ATTR_MAXDATA_SIZE attribute is set, the bind buffer must be large enough to hold the number of characters multiplied by the bytes in each character of the character set.

If `OCI_ATTR_MAXCHAR_SIZE` is set to a nonzero value such as 100, then if the character set has 2 bytes in each character, the minimum possible allocated size is 200 bytes.

The following scenarios demonstrate some examples of the use of the `OCI_ATTR_MAXDATA_SIZE` attribute:

- Scenario 1: CHAR (source data) -> non-CHAR (destination column)

  There are implicit bind conversions of the data. The recommended value of `OCI_ATTR_MAXDATA_SIZE` is the size of the source buffer multiplied by the worst-case expansion factor between the client and server character sets.

- Scenario 2: CHAR (source data) -> CHAR (destination column) or non-CHAR (source data) -> CHAR (destination column)

  The recommended value of `OCI_ATTR_MAXDATA_SIZE` is the size of the column.

- Scenario 3: CHAR (source data) -> PL/SQL variable

  In this case, the recommended value of `OCI_ATTR_MAXDATA_SIZE` is the size of the PL/SQL variable.

### Using OCI_ATTR_MAXCHAR_SIZE Attribute

`OCI_ATTR_MAXCHAR_SIZE` enables processing to work with data in terms of number of characters, rather than number of bytes.

For binds, the `OCI_ATTR_MAXCHAR_SIZE` attribute sets the number of characters reserved on the server to store the bind data.

For example, if OCI_ATTR_MAXDATA_SIZE is set to 100, and `OCI_ATTR_MAXCHAR_SIZE` is set to 0, then the maximum possible size of the data on the server after conversion is 100 bytes. However, if `OCI_ATTR_MAXDATA_SIZE` is set to 300, and `OCI_ATTR_MAXCHAR_SIZE` is set to a nonzero value, such as 100, then if the character set has 2 bytes/character, the maximum possible allocated size is 200 bytes.

For defines, the `OCI_ATTR_MAXCHAR_SIZE` attribute specifies the maximum number of characters that the client application allows in the return buffer. Its derived byte length overrides the `maxlength` parameter specified in the `OCIDefineByPos()` call.

Note: Regardless of the value of the attribute `OCI_ATTR_MAXCHAR_SIZE`, the buffer lengths specified in a bind or define call are always in terms of bytes. The actual length values sent and received by you are also in bytes.

### Buffer Expansion During OCI Binding

Do not set `OCI_ATTR_MAXDATA_SIZE` for OUT binds or for PL/SQL binds. Only set `OCI_ATTR_MAXDATA_SIZE` for INSERT or UPDATE statements.

If neither of these two attributes is set, OCI expands the buffer using its best estimates.

#### IN Binds

If the underlying column was created using character length semantics, then it is preferable to specify the constraint using `OCI_ATTR_MAXCHAR_SIZE`. As long as the actual buffer contains less characters than specified in `OCI_ATTR_MAXCHAR_SIZE`, no constraints are violated at OCI level.

If the underlying column was created using byte length semantics, then use `OCI_ATTR_MAXDATA_SIZE` in the bind handle to specify the byte constraint on the server. If you also specify an `OCI_ATTR_MAXCHAR_SIZE` value, then this constraint is imposed when allocating the receiving buffer on the server side.

#### Dynamic SQL

For dynamic SQL, you can use the explicit describe to get `OCI_ATTR_DATA_SIZE` and `OCI_ATTR_CHAR_SIZE` in parameter handles, as a guide for setting `OCI_ATTR_MAXDATA_SIZE` and `OCI_ATTR_MAXCHAR_SIZE` attributes in bind handles. It is a good practice to specify `OCI_ATTR_MAXDATA_SIZE` and `OCI_ATTR_MAXCHAR_SIZE` to be no more than the actual column width in bytes, or characters.

#### Buffer Expansion During Inserts

You should avoid unexpected behavior caused by buffer expansion during inserts.

Consider what happens when the database column has character length semantics, and the user tries to insert data using `OCIBindByPos()` or `OCIBindByName()` while setting only the `OCI_ATTR_MAXCHAR_SIZE` to 3000 bytes. The database character set is UTF8 and the client character set is ASCII. Then, in this case although 3000 characters will fit in a buffer of size 3000 bytes for the client, on the server side it might expand to more than 4000 bytes. Unless the underlying column is a LONG or a LOB type, the server will return an error. You can get around this problem by specifying the `OCI_ATTR_MAXDATA_SIZE` to be 4000, to guarantee that the data will never exceed 4000 bytes.

### Constraint Checking During Defining

To select data from columns into client buffers, OCI uses defined variables. You can set an `OCI_ATTR_MAXCHAR_SIZE` value on the define buffer to impose an additional character length constraint. There is no `OCI_ATTR_MAXDATA_SIZE` attribute for define handles since the buffer size in bytes serves as the limit on byte length. The define buffer size provided in the `OCIDefineByPos()` call can be used as the byte constraint.

#### Dynamic SQL Selects

When sizing buffers for dynamic SQL, always use the `OCI_ATTR_DATA_SIZE` value in the implicit describe to avoid data loss through truncation. If the database column is created using character length semantics known through `OCI_ATTR_CHAR_USED` attribute, then you can use the `OCI_ATTR_MAXCHAR_SIZE` value to set an additional constraint on the define buffer. A maximum number of `OCI_ATTR_MAXCHAR_SIZE` characters is put in the buffer.

#### Return Lengths

The following length values are always in bytes regardless of the character length semantics of the database:

- The value returned in the `alen`, or the actual length field in binds and defines.

- The value that appears in the length, prefixed in special datatypes like `VARCHAR` and `LONG` `VARCHAR`.

- The value of the indicator variable in case of truncation.

The only exception to this rule is for string buffers in `OCI_UTF16ID` character set id; then the lengths are in UTF-16 units.

Note: The buffer sizes in the bind and define calls and the piece sizes in the `OCIGetPieceInfo()` and `OCISetPieceInfo()` and the callbacks are always in bytes.

### General Compatibility Issues for Character Length Semantics in OCI

- For a release 9.0 or later client talking to an 8.1 or earlier server, `OCI_ATTR_MAXCHAR_SIZE` is not understood by the server, so this value will be ignored. If you specify only this value, OCI will derive the corresponding `OCI_ATTR_MAXDATA_SIZE` value based on the maximum bytes for each character for the client-side character set.

- For an 8.1 or earlier client talking to a 9.0 or later server, the client will never be able to specify an `OCI_ATTR_MAXCHAR_SIZE` value, so the server will consider the client always expecting byte length semantics. This is similar to the situation when the client specifies only `OCI_ATTR_MAXDATA_SIZE`.

So in both cases, the server and client can exchange information in an appropriate manner.

#### Code Example for Inserting and Selecting Using OCI_ATTR_MAXCHAR_SIZE

When a column is created by specifying a number `N` of characters, the actual allocation in the data base will consider the worst scenario in the following table. The real bytes allocated will be a multiple of `N`, say `M` times `N`. Currently, `M` is three as the maximum bytes for each character in UTF-8.

For example, in the following table `EMP`, `ENAME` column is defined as 30 characters and `ADDRESS` is defined as 80 characters. Then the corresponding byte lengths in database are M*30 or 3*30=90, and M*80 or 3*80=240 respectively.

    ...
    utext ename[31], address[81];
    /* E' <= 30+ 1, D' <= 80+ 1, considering null-termination */
    sb4 ename_max_chars = EC=20, address_max_chars = ED=60;
     /* EC <= (E' - 1), ED <= (D' - 1) */
    sb4 ename_max_bytes = EB=80, address_max_bytes = DB=200;
     /* EB <= M * EC, DB <= M * DC */
    text *insstmt = (text *)"INSERT INTO EMP(ENAME, ADDRESS) VALUES (:ENAME, \
    :ADDRESS)";
    text *selstmt = (text *)"SELECT ENAME, ADDRESS FROM EMP";
    ...
    /* Inserting Column Data */
    OCIStmtPrepare(stmthp1, errhp, insstmt, (ub4)strlen((char *)insstmt),
        (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
    OCIBindByName(stmthp1, &bnd1p, errhp, (text *)":ENAME",
        (sb4)strlen((char *)":ENAME"),
        (dvoid *)ename, sizeof(ename), SQLT_STR, (dvoid *)&insname_ind,
        (ub2 *)alenp, (ub2 *)rcodep, (ub4)maxarr_len, (ub4 *)curelep, OCI_DEFAULT);
    /* either */
    OCIAttrSet((dvoid *)bnd1p, (ub4)OCI_HTYPE_BIND, (dvoid *)&ename_max_bytes,
        (ub4)0, (ub4)OCI_ATTR_MAXDATA_SIZE, errhp);
    /* or */
    OCIAttrSet((dvoid *)bnd1p, (ub4)OCI_HTYPE_BIND, (dvoid *)&ename_max_chars,
        (ub4)0, (ub4)OCI_ATTR_MAXCHAR_SIZE, errhp);
    ...
    /* Retrieving Column Data */
    OCIStmtPrepare(stmthp2, errhp, selstmt, strlen((char *)selstmt),
        (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
    OCIDefineByPos(stmthp2, &dfn1p, errhp, (ub4)1, (dvoid *)ename,
       (sb4)sizeof (ename),
       SQLT_STR, (dvoid *)&selname_ind, (ub2 *)alenp, (ub2 *)rcodep,
       (ub4)OCI_DEFAULT);
    /* if not called, byte semantics is by default */
    OCIAttrSet((dvoid *)dfn1p, (ub4)OCI_HTYPE_DEFINE, (dvoid *)&ename_max_chars,
       (ub4)0,
       (ub4)OCI_ATTR_MAXCHAR_SIZE, errhp);
    ...

#### Code Example for UTF-16 Binding and Defining

The character set ID in bind and define of the CHAR or VARCHAR2, or in NCHAR or NVARCHAR variant handles can be set to assume that all data will be passed in UTF-16 (Unicode) encoding. To specify UTF-16, set `OCI_ATTR_CHARSET_ID` = `OCI_UTF16ID`.

See Also: "Bind Handle Attributes"

OCI provides a typedef called `utext` to facilitate binding and defining of UTF-16 data. The internal representation of `utext` is a 16-bit unsigned integer, `ub2`. Operating systems where the encoding scheme of the `wchar_t` datatype conforms to UTF-16 can easily convert `utext` to the `wchar_t` datatype using cast operators.

Even for UTF-16 data, the buffer size in bind and define calls is assumed to be in bytes. Users should use the `utext` datatype as the buffer for input and output data.

The following pseudocode illustrates a bind and define for UTF-16 data:

    ...
    OCIStmt  *stmthp1, *stmthp2;
    OCIDefine *dfn1p, *dfn2p;
    OCIBind *bnd1p, *bnd2p;
    text *insstmt=
          (text *) "INSERT INTO EMP(ENAME, ADDRESS) VALUES (:ename, :address)"; \
    text *selname =
          (text *) "SELECT ENAME, ADDRESS FROM EMP";
    utext ename[21];   /* Name -    UTF-16 */
    utext address[51]; /* Address - UTF-16 */
    ub2 csid = OCI_UTF16ID;
    sb4 ename_col_len = 20;
    sb4 address_col_len = 50;
    ...
    /* Inserting UTF-16 data */
    OCIStmtPrepare (stmthp1, errhp, insstmt, (ub4)strlen ((char *)insstmt),
                    (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
    OCIBindByName (stmthp1, &bnd1p, errhp, (text*)":ENAME",
                  (sb4)strlen((char *)":ENAME"),
                  (dvoid *) ename, sizeof(ename), SQLT_STR,
                  (dvoid *)&insname_ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0,
                  (ub4 *)0, OCI_DEFAULT);
    OCIAttrSet ((dvoid *) bnd1p, (ub4) OCI_HTYPE_BIND, (dvoid *) &csid,
               (ub4) 0, (ub4)OCI_ATTR_CHARSET_ID, errhp);
    OCIAttrSet((dvoid *) bnd1p, (ub4) OCI_HTYPE_BIND, (dvoid *) &ename_col_len,
               (ub4) 0, (ub4)OCI_ATTR_MAXDATA_SIZE, errhp);
    ...
    /* Retrieving UTF-16 data */
    OCIStmtPrepare (stmthp2, errhp, selname, strlen((char *) selname),
                    (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
    OCIDefineByPos (stmthp2, &dfn1p, errhp, (ub4)1, (dvoid *)ename,
                    (sb4)sizeof(ename), SQLT_STR,
                    (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT);
    OCIAttrSet ((dvoid *) dfn1p, (ub4) OCI_HTYPE_DEFINE, (dvoid *) &csid,
                (ub4) 0, (ub4)OCI_ATTR_CHARSET_ID, errhp);
    ...

## PL/SQL REF CURSORs and Nested Tables in OCI

The OCI provides the ability to bind and define PL/SQL `REF` `CURSOR`s and nested tables. An application can use a statement handle to bind and define these types of variables. As an example, consider this PL/SQL block:

    static const text *plsql_block = (text *)
      "begin \
         OPEN :cursor1 FOR SELECT employee_id, last_name, job_id, manager_id, \
                 salary, department_id \
                 FROM employees WHERE job_id=:job ORDER BY employee_id; \
         OPEN :cursor2 FOR SELECT * FROM departments ORDER BY department_id;
      end;";

An application allocates a statement handle for binding, by calling `OCIHandleAlloc()`, and then binds the `:cursor1` placeholder to the statement handle, as in the following code, where `:cursor1` is bound to `stm2p`.

    status = OCIStmtPrepare (stm1p, errhp, (text *) plsql_block,
                 strlen((char *)plsql_block), OCI_NTV_SYNTAX, OCI_DEFAULT);
    ...
    status = OCIBindByName (stm1p, (OCIBind **) &bnd1p, errhp,
                 (text *)":cursor1", (sb4)strlen((char *)":cursor1"),
                 (dvoid *)&stm2p, (sb4) 0,  SQLT_RSET, (dvoid *)0,
                   (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,   (ub4)OCI_DEFAULT);

In this code, `stm1p` is the statement handle for the PL/SQL block, while `stm2p` is the statement handle which is bound as a `REF` `CURSOR` for later data retrieval. A value of SQLT_RSET is passed for the `dty` parameter.

As another example, consider the following:

    static const text *nst_tab = (text *)
           "SELECT last_name, CURSOR(SELECT department_name, location_id \
            FROM  departments)  FROM employees WHERE last_name = 'FORD'";

The second position is a nested table, which an OCI application can define as a statement handle as follows:

    status = OCIStmtPrepare (stm1p, errhp, (text *) nst_tab,
             strlen((char *)nst_tab), OCI_NTV_SYNTAX, OCI_DEFAULT);
    ...
    status = OCIDefineByPos (stm1p, (OCIDefine **) &dfn2p, errhp, (ub4)2,
              (dvoid *)&stm2p, (sb4)0, SQLT_RSET, (dvoid *)0, (ub2 *)0,
                         (ub2 *)0, (ub4)OCI_DEFAULT);

After execution, when you fetch a row into `stm2p` it becomes a valid statement handle.

Note: If you have retrieved multiple `REF` `CURSOR`s, you must take care when fetching them into `stm2p`. If you fetch the first one, you can then perform fetches on it to retrieve its data. However, once you fetch the second `REF` `CURSOR` into `stm2p`, you no longer have access to the data from the first `REF` `CURSOR`. OCI does not support PL/SQL `REF` `CURSORs` that were executed in scrollable mode. OCI does not support scrollable `REF` `CURSOR`s because you cannot scroll back to the rows already fetched by a `REF` `CURSOR`.

## Runtime Data Allocation and Piecewise Operations in OCI

You can use the OCI to perform piecewise inserts, updates, and fetches of data. You can also use the OCI to provide data dynamically in case of array inserts or updates, instead of providing a static array of bind values. You can insert or retrieve a very large column as a series of chunks of smaller size, minimizing client-side memory requirements.

The size of individual pieces is determined at runtime by the application and can be uniform or not.

The piecewise functionality of OCI is particularly useful when performing operations on extremely large blocks of string or binary data, operations involving database columns that store `CLOB`, `BLOB`, `LONG`, `RAW`, or `LONG` `RAW` data.

The piecewise fetch is complete when the final `OCIStmtFetch()` call returns a value of OCI_SUCCESS.

In both the piecewise fetch and insert, it is important to understand the sequence of calls necessary for the operation to complete successfully. For a piecewise insert, you must call `OCIStmtExecute()` one time more than the number of pieces to be inserted (if callbacks are not used). This is because the first time `OCIStmtExecute()` is called, it merely returns a value indicating that the first piece to be inserted is required. As a result, if you are inserting n pieces, you must call `OCIStmtExecute()` a total of n+1 times.

Similarly, when performing a piecewise fetch, you must call `OCIStmtFetch()` once more than the number of pieces to be fetched.

Users who are binding to PL/SQL index-by tables can retrieve a pointer to the current index of the table during the `OCIStmtGetPieceInfo()` calls.

### Valid Datatypes for Piecewise Operations

Only some datatypes can be manipulated in pieces. OCI applications can perform piecewise fetches, inserts, or updates of all the following datatypes:

- `VARCHAR2`

- `STRING`

- `LONG`

- `LONG` `RAW`

- `RAW`

- `CLOB`

- `BLOB`

Another way of using this feature for all datatypes is to provide data dynamically for array inserts or updates. The callbacks should always specify `OCI_ONE_PIECE` for the `piecep` parameter of the callback for datatypes that do not support piecewise operations.

### Types of Piecewise Operations

You can perform piecewise operations in two ways:

- Use calls provided in the OCI library to execute piecewise operations under a polling paradigm.

- Employ user-defined callback functions to provide the necessary information and data blocks.

When you set the `mode` parameter of an `OCIBindByPos()` or `OCIBindByName()` call to `OCI_DATA_AT_EXEC`, it indicates that an OCI application will be providing data for an `INSERT` or `UPDATE` dynamically at runtime.

Similarly, when you set the `mode` parameter of an `OCIDefineByPos()` call to `OCI_DYNAMIC_FETCH`, it indicates that an application will dynamically provide allocation space for receiving data at the time of the fetch.

In each case, you can provide the run-time information for the `INSERT`, `UPDATE`, or `FETCH` in one of two ways: through callback functions, or by using piecewise operations. If callbacks are desired, an additional bind or define call is necessary to register the callbacks.

The following sections give specific information about run-time data allocation and piecewise operations for inserts, updates, and fetches.

Note: Piecewise operations are also valid for SQL and PL/SQL blocks.

### Providing INSERT or UPDATE Data at Runtime

When you specify the `OCI_DATA_AT_EXEC` mode in a call to `OCIBindByPos()` or `OCIBindByName()`, the `value_sz` parameter defines the total size of the data that can be provided at runtime. The application must be ready to provide to the OCI library the run-time `IN` data buffers on demand as many times as is necessary to complete the operation. When the allocated buffers are no longer required, they must be freed by the client.

Runtime data is provided in one of the two ways:

- You can define a callback using the `OCIBindDynamic()` function, which when called at runtime returns either a piece or the whole data.

- If no callbacks are defined, the call to `OCIStmtExecute()` to process the SQL statement returns the `OCI_NEED_DATA` error code. The client application then provides the `IN/OUT` data buffer or piece using the `OCIStmtSetPieceInfo()` call that specifies which bind and piece are being used.

#### Performing a Piecewise Insert or Update

Once the OCI environment has been initialized, and a database connection and session have been established, a piecewise insert begins with calls to prepare a SQL or PL/SQL statement and to bind input values. Piecewise operations using standard OCI calls rather than user-defined callbacks do not require a call to `OCIBindDynamic()`.

Note: Additional bind variables that are not part of piecewise operations may require additional bind calls, depending on their datatypes.

Following the statement preparation and bind, the application performs a series of calls to `OCIStmtExecute()`, `OCIStmtGetPieceInfo()` and `OCIStmtSetPieceInfo()` to complete the piecewise operation. Each call to `OCIStmtExecute()` returns a value that determines what action should be performed next. In general, the application retrieves a value indicating that the next piece needs to be inserted, populates a buffer with that piece, and then executes an insert. When the last piece has been inserted, the operation is complete.

Keep in mind that the insert buffer can be of arbitrary size and is provided at runtime. In addition, each inserted piece does not need to be of the same size. The size of each piece to be inserted is established by each `OCIStmtSetPieceInfo()` call.

Note: If the same piece size is used for all inserts, and the size of the data being inserted is not evenly divisible by the piece size, the final inserted piece will be smaller. You must account for this by indicating the smaller size in the final `OCIStmtSetPieceInfo()` call.

The procedure is illustrated in Figure 5-3.

Figure 5-3 Performing Piecewise Insert

Description of "Figure 5-3 Performing Piecewise Insert"

1. Initialize the OCI environment, allocate the necessary handles, connect to a server, authorize a user, and prepare a statement request.

2. Bind a placeholder using `OCIBindByName()` or `OCIBindByPos()`. You do not need to specify the actual size of the pieces you will use, but you must provide the total size of the data that can be provided at runtime.

3. Call `OCIStmtExecute()` for the first time. No data is being inserted here, and the `OCI_NEED_DATA` error code is returned to the application. If any other value is returned, it indicates that an error occurred.

4. Call `OCIStmtGetPieceInfo()` to retrieve information about the piece that needs to be inserted. The parameters of `OCIStmtGetPieceInfo()` include a pointer to a value indicating if the required piece is the first piece, `OCI_FIRST_PIECE`, or a subsequent piece, `OCI_NEXT_PIECE`.

5. The application populates a buffer with the piece of data to be inserted and calls `OCIStmtSetPieceInfo()` with these parameters:

   - a pointer to the piece

   - a pointer to the length of the piece

   - a value indicating whether this is the

   1. first piece, `OCI_FIRST_PIECE`

   2. an intermediate piece, `OCI_NEXT_PIECE`

   3. the last piece, `OCI_LAST_PIECE`

6. Call `OCIStmtExecute()` again. If `OCI_LAST_PIECE` was indicated in step 5 and `OCIStmtExecute()` returns `OCI_SUCCESS`, all pieces were inserted successfully. If `OCIStmtExecute()` returns `OCI_NEED_DATA`, go back to Step 3 for the next insert. If `OCIStmtExecute()` returns any other value, an error occurred.

The piecewise operation is complete when the final piece has been successfully inserted. This is indicated by the `OCI_SUCCESS` return value from the final `OCIStmtExecute()` call.

Piecewise updates are performed in a similar manner. In a piecewise update operation the insert buffer is populated with data that is being updated and `OCIStmtExecute()` is called to execute the update.

### Piecewise Operations with PL/SQL

An OCI application can perform piecewise operations with PL/SQL for `IN`, `OUT`, and `IN/OUT` bind variables in a method similar to that outlined previously. Keep in mind that all placeholders in PL/SQL statements are bound, rather than defined. The call to `OCIBindDynamic()` specifies the appropriate callbacks for `OUT` or `IN/OUT` parameters.

### Providing FETCH Information at Runtime

When a call is made to `OCIDefineByPos()` with the `mode` parameter set to `OCI_DYNAMIC_FETCH`, an application can specify information about the data buffer at the time of fetch. You may also need to call `OCIDefineDynamic()` to set callback function that will be invoked to get information about your data buffer.

Run-time data is provided in one of the two ways:

- You can define a callback using the `OCIDefineDynamic()`. The `value_sz` parameter defines the maximum size of the data that will be provided at runtime. When the client library needs a buffer to return the fetched data, the callback will be invoked to provide a run-time buffer into which a either piece or the whole data will be returned.

- If no callbacks are defined, the `OCI_NEED_DATA` error code is returned and the `OUT` data buffer or piece can then be provided by the client application using `OCIStmtSetPieceInfo()`. The `OCIStmtGetPieceInfo()` call provides Information about which define and which piece are involved.

#### Performing a Piecewise Fetch

The fetch buffer can be of arbitrary size. In addition, each fetched piece does not need to be of the same size. The only requirement is that the size of the final fetch must be exactly the size of the last remaining piece. The size of each piece to be fetched is established by each `OCIStmtSetPieceInfo()` call. This process is illustrated in Figure 5-4.

Figure 5-4 Performing Piecewise Fetch

Description of "Figure 5-4 Performing Piecewise Fetch"

1. Initialize the OCI environment, allocate necessary handles, connect to a database, authorize a user, prepare a statement, and execute the statement.

2. Define an output variable using `OCIDefineByPos()`, with `mode` set to `OCI_DYNAMIC_FETCH`. At this point you do not need to specify the actual size of the pieces you will use, but you must provide the total size of the data that will be fetched at runtime.

3. Call `OCIStmtFetch()` for the first time. No data is retrieved, and the `OCI_NEED_DATA` error code is returned to the application. If any other value is returned, an error occurred.

4. Call `OCIStmtGetPieceInfo()` to obtain information about the piece to be fetched. The `piecep` parameter indicates whether it is the first piece, `OCI_FIRST_PIECE`, a subsequent piece, `OCI_NEXT_PIECE`, or the last piece, `OCI_LAST_PIECE`.

5. Call `OCIStmtSetPieceInfo()` to specify the fetch buffer.

6. Call `OCIStmtFetch()` again to retrieve the actual piece. If `OCIStmtFetch()` returns `OCI_SUCCESS`, all the pieces have been fetched successfully. If `OCIStmtFetch()` returns `OCI_NEED_DATA`, return to Step 4 to process the next piece. If any other value is returned, an error occurred.

### Piecewise Binds and Defines for LOBs

There are two ways of doing piecewise binds and defines for LOBs:

1. Using the data interface

   You can bind or define character data for `CLOB` columns using `SQLT_CHR` (`VARCHAR2`) or `SQLT_LNG` (`LONG`) as the input datatype for the following functions. You can also bind or define raw data for BLOB columns using `SQLT_LBI` (`LONG` `RAW`), and `SQLT_BIN` (`RAW`) as the input datatype for these functions:

   - OCIDefineByPos()

   - OCIBindByName()

   - OCIBindByPos(}

     See Also: "Binding LOB Data" for usage and examples for both `INSERT` and `UPDATE statements` "Defining LOB Data" for usage and examples of `SELECT` statements

   All the piecewise operations described later are supported for `CLOB` and `BLOB` columns in this case.

2. Using the LOB locator

   You can bind or define a LOB locator for `CLOB` and `BLOB` columns using `SQLT_CLOB` (`CLOB`) or `SQLT_BLOB` (`BLOB`) as the input datatype for the following functions.

   - OCIDefineByPos()

   - OCIBindByName()

   - OCIBindByPos(}

   You must then call OCILob* functions to read and manipulate the data. `OCILobRead2()` and `OCILobWrite2()` support piecewise and callback modes.

See Also: "OCILobRead2()" "OCILobWrite2()" "LOB Read and Write Callbacks" for information about streaming using callbacks with `OCILobWrite2()` and `OCILobRead2()`.